package cn.gson.fengboot.model.dao;

import cn.gson.fengboot.model.pojos.Ltousu;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import java.util.List;

public interface LtousuDao extends CrudRepository<Ltousu,Integer> {
    //圆形统计图类型分布查询
    @Query(nativeQuery = true,value = "select count(*) from ltousu where  ts_fenglei=?1 and ts_zt =1")
    public Integer lxcs(String Type);

    //圆形统计图花费时间分布查询
    @Query(nativeQuery = true,value = "select count(*) from ltousu where  ts_huafeitime=?1 and ts_zt =1 ORDER BY ts_riqi ASC")
    public Integer hfsjcs(String Hfsj);

    //柱形统计图投诉月度统计分布查询
    @Query(nativeQuery = true,value = "select count(*),ts_riqi from ltousu where YEAR(ts_riqi)=YEAR(NOW()) and  ts_zt=1 GROUP BY  month(ts_riqi) ORDER BY ts_riqi ASC;")
    public  List<Integer> ydtsfb();

    //柱形统计图投诉月度统计分布查询
    @Query(nativeQuery = true,value = "select ts_riqi from ltousu where YEAR(ts_riqi)=YEAR(NOW()) and ts_zt=1 GROUP BY  month(ts_riqi) ORDER BY ts_riqi ASC;")
    public  List<String> ydtsfb1();

    //柱形统计图投诉客户投诉分布查询
    @Query(nativeQuery = true,value = "SELECT count(*),d.client_name FROM ltousu e LEFT JOIN tfyclient_form d  on e.client_id = d.client_id where d.client_name =?1")
    public  Integer khtsfb(String name);

//    //折线统计图月度统计次数查询
//    @Query(nativeQuery = true,value = "select ts_jinjichendu,count(*),ts_riqi from ltousu  where DATE_FORMAT(ts_riqi,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') and ts_zt=1 and  ts_jinjichendu =?1")
//    public  List<Integer> zxydjjcdcs(String jjcd);
//
//    //折线统计图月度紧急程度统计分布查询
//    @Query(nativeQuery = true,value = "select ts_jinjichendu,ts_riqi from ltousu  where DATE_FORMAT(ts_riqi,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') and ts_zt=1 GROUP BY ts_jinjichendu ORDER BY ts_riqi ASC;")
//    public  List<Ltousu> zxydjjcdcs1();

    //折线统计图月度处理过程统计分布查询
    @Query(nativeQuery = true,value = "select ts_chulijieguo,ts_riqi from ltousu  where month(ts_riqi) = month(ts_riqi) and ts_zt=1 GROUP BY year(ts_riqi)  ORDER BY ts_riqi ASC")
    public  List<String> zxccgcdcs();

    //折线统计图月度统计次数查询
    @Query(nativeQuery = true,value = "select count(*) from ltousu  where DATE_FORMAT(ts_riqi,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') and ts_zt=1 and  ts_jinjichendu =?1")
    public  Integer zhexian(String jjcd);



    //折线统计图月度统计次数查询
    @Query(nativeQuery = true,value = "select count(*) from ltousu  where DATE_FORMAT(ts_riqi,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') and ts_zt=1 and  ts_chulijieguo =?1")
    public  Integer zhexian1(String jjcd);
}
